Certain database systems today may provide a complex data management environment. For example, MICROSOFT SQL SERVER is a comprehensive database management platform that provides extensive management and development tools, a powerful extraction, transformation, and loading (ETL) tool, business intelligence and analysis services, and other capabilities. Among other improvements, the MICROSOFT WINDOWS .NET Framework Common Language Runtime (CLR), a runtime environment, recently has been integrated into the SQL SERVER database.
The CLR is an important part of the MICROSOFT .NET Framework, and provides the execution environment for all .NET code. Thus, code that runs within the CLR is referred to as “managed code”. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security. The CLR is now loaded by SQL SERVER upon the first invocation of a .NET routine.
In previous versions of SQL SERVER, database programmers were limited to using Transact-SQL when writing code on the server side. Transact-SQL is an extension of the SQL language as defined by the International Standards Organization (ISO) and the American National Standards Institute (ANSI). Using Transact-SQL, database developers can create, modify and delete databases and tables, as well as insert, retrieve, modify and delete data stored in a database. Transact-SQL is specifically designed for direct structural data access and manipulation. While Transact-SQL excels at set-based operations over data, it is not a full-fledged programming language as are VISUAL BASIC .NET and C#. For example, Transact-SQL does not support arrays, collections, for each loops, bit shifting or classes.
With the CLR integrated into the SQL SERVER database, database developers can now perform tasks that were impossible or difficult to achieve with Transact-SQL alone. Both VISUAL BASIC .NET and C# are modem programming languages offering full support for arrays, structured exception handling, and collections. Developers can leverage CLR integration to write code that has more complex logic and is more suited for computation tasks using languages such as VISUAL BASIC .NET and C#. These programming languages offer object-oriented capabilities such as encapsulation, inheritance and polymorphism. Related code can be easily organized into classes and namespaces.
Managed code is better suited than Transact-SQL for number crunching and complicated execution logic, and features extensive support for many complex tasks, including string handling and regular expressions. With the functionality found in the .NET Framework Base Class Library (BCL), database developers have access to thousands of pre-built classes and routines which can be easily accessed from any stored procedure, trigger or user defined function.
With the CLR runtime environment integrated in the SQL SERVER database, transactions can be performed locally, thus cutting down on operation costs. For example, instead of having a client server sending requests to a database and then obtaining results, data manipulations can be done locally in the database itself. This set-up may be used in the context of transactions that may be distributed over networks of databases and client servers. In such a scenario, it would be advantageous to provide for mechanisms that allow for the coordination of the transactions both within database systems and across system across networks.